﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

using System.Configuration;

namespace Corsat.Library.DataManager
{
    public static class Main
    {
        #region Main Variables

        static SqlConnection connection;
        static SqlCommand command;
        static DataSet dsMaster;
        static DataTable dtMaster;
        static SqlDataAdapter adapter;

        #endregion

        /// <summary>
        /// Data Manager's main static constructor.
        /// </summary>
        static Main()
        {
            connection = new SqlConnection(ConfigurationSettings.AppSettings["LocalConnectionString"]);
            command = new SqlCommand("", connection);
            adapter = new SqlDataAdapter(command);

            dsMaster = new DataSet();
            dtMaster = new DataTable();
        }

        #region Private Methods

        static void OpenConnection()
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
        }

        static void CloseConnection()
        {
            if (connection.State != ConnectionState.Closed)
                connection.Close();
        }
        static DataTable GetData(StoredProcedure StoredProcedureName, SqlParameter[] Parameters)
        {
            try
            {
                command.CommandText = StoredProcedureName.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                if (Parameters != null)
                    command.Parameters.AddRange(Parameters);

                dtMaster = new DataTable();
                adapter.Fill(dtMaster);

                return dtMaster;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        static SqlParameter CreateParameter(string Name, object Value)
        {

            SqlParameter param = command.CreateParameter();
            param.ParameterName = Name;
            param.Value = Value;

            return param;
        }

        /// <summary>
        /// Converts the contents of a given DataTable to a List of Items.
        /// </summary>
        /// <param name="ItemsDataTable"></param>
        /// <returns></returns>
        static List<Logic.Item> ConvertTableToItems(DataTable ItemsDataTable)
        {
            try
            {
                List<Logic.Item> Items = new List<Logic.Item>();
                DataRow drItem;

                for (int i = 0; i < ItemsDataTable.Rows.Count; i++)
                {
                    Logic.Item item = new Logic.Item();
                    drItem = ItemsDataTable.Rows[i];

                    item.Id = Convert.ToInt32(drItem["ItemId"]);
                    item.Value = Convert.ToString(drItem["ItemName"]);

                    if (drItem["Description"] != DBNull.Value && drItem["Description"] != null && !String.IsNullOrEmpty(drItem["Description"].ToString()))
                        item.Description = Convert.ToString(drItem["Description"]);

                    Item Category = new Item();
                    Category.Id = Convert.ToInt32(drItem["CategoryId"]);
                    Category.Value = Convert.ToString(drItem["CategoryName"]);
                    item.Category = Category;

                    if (drItem["ParentItemId"] != DBNull.Value && drItem["ParentItemId"] != null && !String.IsNullOrEmpty(drItem["ParentItemId"].ToString()))
                    {
                        Item ParentItem = new Item();
                        ParentItem.Id = Convert.ToInt32(drItem["ParentItemId"]);
                        ParentItem.Value = Convert.ToString(drItem["ParentItemName"]);
                        item.Parent = ParentItem;
                    }

                    Items.Add(item);
                }

                return Items;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        /// <summary>
        /// Converts the contents of a given DataTable to a List of Items.
        /// </summary>
        /// <param name="ItemsDataTable"></param>
        /// <returns></returns>
        static List<Item> ConvertTableToSmallItems(DataTable ItemsDataTable)
        {
            try
            {
                List<Item> Items = new List<Item>();
                DataRow drItem;

                for (int i = 0; i < ItemsDataTable.Rows.Count; i++)
                {
                    Item item = new Item();
                    drItem = ItemsDataTable.Rows[i];

                    item.Id = Convert.ToInt32(drItem["ItemId"]);
                    item.Value = Convert.ToString(drItem["ItemName"]);

                    Items.Add(item);
                }

                return Items;
            }
            catch (Exception exc)
            {
                return null;
            }
        }


        static int GenerateNewAccountCorsatNumber()
        {
            try
            {
                DataTable dtAllCorsatNumbers = GetData(StoredProcedure.sp_GetAllAccountsCorsatNumbers, null);

                Random random = new Random();
                int randomNumber = 0;

                bool IsNewNumber = false;

                do
                {
                    randomNumber = random.Next(1001, 9999);

                    DataRow[] drNumbers = dtAllCorsatNumbers.Select("CorsatId = " + randomNumber);

                    if (drNumbers.Length > 0)
                        IsNewNumber = false;
                    else
                        IsNewNumber = true;

                } while (!IsNewNumber);

                return randomNumber;
            }
            catch (Exception exc)
            {
                return 0;
            }
        }


        #endregion

        public static List<Item> GetAllItemsByCategory(Category Category)
        {
            try
            {
                SqlParameter paramCategory = new SqlParameter("CategoryId", Convert.ToInt32(Category));
                dtMaster = GetData(StoredProcedure.sp_GetAllItemsByCategory, new SqlParameter[] { paramCategory });

                List<Item> Items = ConvertTableToSmallItems(dtMaster);

                return Items;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        public static List<Item> GetAllItemsByCategoryAndParentItem(Category Category, int parentItemId)
        {
            try
            {
                SqlParameter paramCategory = new SqlParameter("CategoryId", Convert.ToInt32(Category));
                SqlParameter paramParentItem = new SqlParameter("ParentItemId", Convert.ToInt32(parentItemId));
                dtMaster = GetData(StoredProcedure.sp_GetAllItemsByCategoryAndParentItem, new SqlParameter[] { paramCategory, paramParentItem });

                List<Item> Items = ConvertTableToSmallItems(dtMaster);

                return Items;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        public static List<Logic.Item> GetAllRelatedItems(int CategoryId, int ParentItemId)
        {
            try
            {
                command.CommandText = StoredProcedure.SP_GetRelatedItems.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                command.Parameters.Add(CreateParameter("CategoryId", CategoryId));
                command.Parameters.Add(CreateParameter("ParentItemId", ParentItemId));

                dtMaster = new DataTable();

                adapter.Fill(dtMaster);

                List<Logic.Item> Items = ConvertTableToItems(dtMaster);

                return Items;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        public static int InsertAccount(Logic.Account Account)
        {
            try
            {
                command.CommandText = StoredProcedure.SP_InsertAccount.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                command.Parameters.Add(CreateParameter("ArabicName", Account.ArabicName));
                command.Parameters.Add(CreateParameter("SSN", Account.SSN));
                command.Parameters.Add(CreateParameter("IsOnlineRegistered", Convert.ToInt32(Account.IsOnlineRegistered)));
                command.Parameters.Add(CreateParameter("IsActivated", Convert.ToInt32(Account.IsActivated)));

                OpenConnection();
                object objAccountId = command.ExecuteScalar();
                CloseConnection();

                int AccountId = Convert.ToInt32(objAccountId);

                return AccountId;
            }
            catch
            {
                return 0;
            }
        }

        public static int RegisterAccount(Logic.Account Account, List<ChosenCourse> Courses)
        {
            try
            {
                int CorsatId = 0;
                CorsatId = GenerateNewAccountCorsatNumber();

                if (CorsatId == 0) return 0;

                command.CommandText = StoredProcedure.SP_InsertAccount.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                command.Parameters.Add(CreateParameter("ArabicName", Account.ArabicName));
                command.Parameters.Add(CreateParameter("SSN", Account.SSN));
                command.Parameters.Add(CreateParameter("IsOnlineRegistered", Convert.ToInt32(Account.IsOnlineRegistered)));
                command.Parameters.Add(CreateParameter("IsActivated", Convert.ToInt32(Account.IsActivated)));
                command.Parameters.Add(CreateParameter("ChurchId", Account.Church.Id));
                command.Parameters.Add(CreateParameter("DioceseId", Account.Diocese.Id));
                command.Parameters.Add(CreateParameter("Email", Account.Email));
                command.Parameters.Add(CreateParameter("BirthDate", Account.BirthDate));
                command.Parameters.Add(CreateParameter("Year", DateTime.Now.Year));
                command.Parameters.Add(CreateParameter("CorsatId", CorsatId));

                OpenConnection();
                object objAccountId = command.ExecuteScalar();
                CloseConnection();

                if (objAccountId == DBNull.Value || Convert.ToInt32(objAccountId) == 0)
                    return 0;
                else
                    return CorsatId;
            }
            catch (Exception exc)
            {
                return 0;
            }
        }

        public static List<Item> GetCourseLevels(int CourseTitleId)
        {
            try
            {
                command.CommandText = StoredProcedure.sp_GetCourseLevels.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                command.Parameters.Add(CreateParameter("CourseTitleId", CourseTitleId));

                dtMaster = new DataTable();

                adapter.Fill(dtMaster);

                List<Item> Items = ConvertTableToSmallItems(dtMaster);

                return Items;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        public static List<Item> Search(string AccountName, string SSN, string AccountNumber)
        {
            if (AccountName.Length > 0)
                command.CommandText = "SELECT AccountId ItemId, ArabicName ItemName FROM AccountsMaster WHERE ArabicName LIKE '%" + AccountName + "%'";
            else if (SSN.Length > 0)
                command.CommandText = "SELECT AccountId ItemId, ArabicName ItemName FROM AccountsMaster WHERE SSN = '" + SSN + "'";
            else if (AccountNumber.Length > 0)
                command.CommandText = "SELECT AccountId ItemId, ArabicName ItemName FROM AccountsMaster inner join AccountsDetails on AccountsMaster.AccountId = AccountsDetails.AccountId WHERE AccountsDetails.CorsatId = " + AccountNumber;

            command.CommandType = CommandType.Text;

            DataTable dtMaster = new DataTable();

            adapter.Fill(dtMaster);

            List<Item> Items = ConvertTableToSmallItems(dtMaster);

            return Items;
        }

        public static Logic.Account GetAccountById(int AccountId)
        {
            try
            {
                command.CommandText = @"
SELECT * FROM AccountsMaster where AccountsMaster.AccountId = " + AccountId.ToString() + @"
SELECT * FROM AccountsDetails where AccountsDetails.AccountId = " + AccountId.ToString() + @" AND AccountsDetails.[Year] = " + DateTime.Now.Year.ToString() + @"                
SELECT * FROM AccountCourses Where AccountId = " + AccountId.ToString() + @"
SELECT * FROM AccountTelephones WHERE AccountId = " + AccountId.ToString() + @"
";

                DataSet dsAccount = new DataSet();

                adapter.Fill(dsAccount);

                Logic.Account account = new Logic.Account();

                DataRow drAccountsMaster = dsAccount.Tables[0].Rows[0];

                account.AccountId = AccountId;
                account.ArabicName = drAccountsMaster["ArabicName"].ToString();
                account.BirthDate = Convert.ToDateTime(drAccountsMaster["BirthDate"]);
                account.Email = drAccountsMaster["Email"].ToString();
                account.IsOnlineRegistered = Convert.ToBoolean(drAccountsMaster["IsOnlineRegistered"]);
                account.SSN = drAccountsMaster["SSN"].ToString();

                DataRow drAccountsDetails = dsAccount.Tables[1].Rows[0];

                account.CorsatId = Convert.ToInt32(drAccountsDetails["CorsatId"]);

                return account;


            }
            catch (Exception exc)
            {
                return new Logic.Account();
            }
        }

    }
}
